﻿using HandleSign.DataBase.Base;
using HandleSign.models;
using HandleSign.Models;
using ServerPA.CompletPort.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace HandleSign.DataBase.SignTime
{
    public class SignTimeDAL
    {
        DataBaseDao DBdao;
        public SignTimeDAL(String connstr)
        {

            DBdao = new DataBaseDao(connstr);

        }

        /// <summary>
        /// 获取调课的classID
        /// </summary>
        /// <param name="tnList"></param>
        /// <returns></returns>        
        public List<ClassSwitchModel> getCSClass()
        {
            List<ClassSwitchModel> classIdList = new List<ClassSwitchModel>();
            ClassSwitchModel csModel;
            string classSwitchsql = "SELECT [scs_TaskNum],[scs_ClassId] FROM [PhoneSign].[dbo].[P_SwitchCourseSign] where scs_Bind = 0";
            DataTable dt_classSwitch = DBdao.Query_dt(classSwitchsql);

            foreach (DataRow taskNumRow in dt_classSwitch.Rows)
            {
                csModel = new ClassSwitchModel();
                csModel.classId = taskNumRow["scs_ClassId"].ToString();
                csModel.classSwitchId = taskNumRow["scs_TaskNum"].ToString();
                classIdList.Add(csModel);
            }
            return classIdList;
        }


        /// <summary>
        /// 
        /// 
        /// 
        /// </summary>
        public List<TaskNumModel> handleTaskNumId(List<TaskNumModel> tnList)
        {

            //string tasknumsql = "select [sthd_ScanTaskNum] ,class_id,st_ScanTime from ( select class_id,[sthd_ScanTaskNum],min(st_ScanTime)st_ScanTime from [PhoneSign].[dbo].StudentSignTime where [IsHandle]=2  and St_SsidScan<>'0' and st_scanStatus = 1  and St_ScanTaskNum != 'NULL' group by [St_ScanTaskNum],class_id  )t order by st_ScanTime";
            //428
            string tasknumsql = "select [sthd_ScanTaskNum] ,st_ScanTime from(select[sthd_ScanTaskNum], min(st_ScanTime) st_ScanTime from[PhoneSign].[dbo].[SignTimeBindHandle] where [sthd_IsHandle]= 2  and St_SsidScan<> '0' and st_scanStatus = 1  and sthd_ScanTaskNum != 'NULL' group by sthd_ScanTaskNum)t order by st_ScanTime";

            DataTable dt_tasknum = DBdao.Query_dt(tasknumsql);

            for (int i = dt_tasknum.Rows.Count - 1; i >= 0; i--)  //  *** 倒序
            {
                string taskNumId = string.IsNullOrEmpty(dt_tasknum.Rows[i]["sthd_ScanTaskNum"].ToString()) ? "NULL" : dt_tasknum.Rows[i]["sthd_ScanTaskNum"].ToString();
                //  暂时不处理 TaskNum为NULL的情况
                if (!taskNumId.Equals("NULL"))
                {

                    List<TaskNumModel> tnListOfTaskNumId = tnList.Where(u => u.taskNumId == taskNumId).ToList();  // 判断TaskNum是否已经存在

                    if (tnListOfTaskNumId.Count > 0)
                    {
                        //1、TaskNum存在
                        //if (!(tnListOfTaskNumId[0].classList.Where(id => id.Equals(classId)).ToList().Count>0))  // 判断班级是否存在
                        //{
                        //    //2、班级不存在
                        //    // 需要测试顺序是否正确，和会不会出现重复的情况
                        //    TaskNumModel tnModel = tnListOfTaskNumId[0];
                        //    int index = tnList.IndexOf(tnModel);
                        //    tnList.Remove(tnModel);
                        //    tnModel.classList.Add(classId);
                        //    tnList.Insert(index, tnModel);
                        //}
                    }
                    //1、TaskNum还未存在
                    else
                    {
                        TaskNumModel taskNumModel = setFinishTime(taskNumId);

                        if (taskNumModel != null)
                        {
                            tnList.Add(taskNumModel);
                        }
                    }
                }
            }


            string TaskNumModelStr = string.Empty;
            foreach (TaskNumModel t in tnList)
            {
                TaskNumModelStr += "\r\n" + t.tostring();
            }
            String logStr = "\r\n写入时间：" + DateTime.Now.ToString() + "\r\n" + "TaskNumList: " + TaskNumModelStr;

            return tnList;

        }


        /// <summary>
        /// 前提: 传入ClassID集合
        /// 获取所有的Class（List）
        /// 包挎新建的，旧的，调课的，空的
        /// </summary>
        //public List<TaskNumModel> handleClassId(List<TaskNumModel> tnList)
        //{
        //    //time  从前往后排序
        //    //string tasknumsql = "select [St_ScanTaskNum] from ( select [St_ScanTaskNum],min(st_ScanTime)st_ScanTime from [PhoneSign].[dbo].[P_SignTime] where [IsHandle]=2 group by [St_ScanTaskNum]  )t order by st_ScanTime";

        //    string classIdsql = "select [St_ScanTaskNum] ,class_id,st_ScanTime from ( select class_id,[St_ScanTaskNum],min(st_ScanTime)st_ScanTime from [PhoneSign].[dbo].StudentSignTime where [IsHandle]=2 group by [St_ScanTaskNum],class_id  )t order by st_ScanTime";
        //    DataTable dt_classId = DBdao.Query_dt(classIdsql);

        //    for (int i = dt_classId.Rows.Count - 1; i >= 0; i--)  //  *** 倒序
        //    {
        //        // taskNum 为空时，设置其TaskNumID 未 NULL
        //        string taskNumId = string.IsNullOrEmpty(dt_classId.Rows[i]["St_ScanTaskNum"].ToString()) ? "NULL" : dt_classId.Rows[i]["St_ScanTaskNum"].ToString();
        //        string classId = dt_classId.Rows[i]["class_id"].ToString();
        //        string startTime = dt_classId.Rows[i]["st_ScanTime"].ToString();
        //        //-------------------------------------------------------------------------------------------------------------------------------------

        //        List<TaskNumModel> tnListofClass = tnList.Where(u => u.classId == classId).ToList();  //  2 /5



        //        /// 处理前面是null 的情况
        //        //if (tnListofClass.Count() > 0 && !taskNumId.Equals("NUll") && tnListofClass[tnListofClass.Count()-1].taskNumId.Equals("NULL")) {


        //        //   TaskNumModel nulltnList = tnList.Where(u => u.taskNumId.Equals("NULL")).ToList()[0];
        //        //   int index  =  tnList.IndexOf(nulltnList);
        //        //    nulltnList.taskNumId = taskNumId; // 只允许同一个class中，一个taskNum 为NULL 
        //        //    tnList.Remove(nulltnList);
        //        //    tnList.Insert(index, nulltnList);

        //        //}
        //        //List<TaskNumModel> list = tnList.Where(u => u.classId == classId && u.taskNumId == taskNumId).ToList();  // 两个同时成立时 ，才算同一节课。否者新建TaskNumModel
        //        ////-------------------------------------------------------------------------------------------------------------------------------------

        //        //// 主逻辑
        //        //if (list.Count() == 0) // 之前没有的
        //        //{
        //        //    tnList.Add(newTnModel(classId,taskNumId));
        //        //}

        //        if (tnListofClass.Count > 0)
        //        {
        //            TaskNumModel taskNumModel = tnListofClass[tnListofClass.Count - 1];  // 每次都只处理最后一条
        //            if (taskNumModel.taskNumId.Equals("NULL") && !taskNumId.Equals("NULL"))
        //            {
        //                    TaskNumModel nulltnList = tnList.Where(u => u.taskNumId.Equals("NULL")).ToList()[0];
        //                    int index = tnList.IndexOf(nulltnList);
        //                    nulltnList.taskNumId = taskNumId; // 只允许同一个class中，一个taskNum 为NULL 
        //                    tnList.Remove(nulltnList);
        //                    tnList.Insert(index, nulltnList);
        //                    //  还需要update一下下课时间
        //            }
        //            else  if (taskNumId.Equals("NULL") || !taskNumId.Equals(taskNumModel.taskNumId)) {
        //                    tnList.Add(setFinishTime(classId, taskNumId));
        //                }
        //        }
        //        else  //tnlist还未存在
        //        {
        //            tnList.Add(setFinishTime(classId, taskNumId));
        //        }
        //    }
        //    return tnList;
        //}


        public TaskNumModel setFinishTime(string taskNumId)
        {

            TaskNumModel tnModel = null;
            String taskClassId = getTaskClassId(taskNumId);
            if (taskClassId == null)
            {
                return tnModel;
            }

            // 未存在,则新建
            string finishtimesql = "select time_num,classEndtime,class_sgroupid from [AccessDB].[dbo].[TaskBindNum] where Stb_ScanTaskNum = '" + taskNumId + "' ";

            DataTable dt_finishTime = DBdao.Query_dt(finishtimesql);

            if (dt_finishTime.Rows.Count > 0)
            {
                // DateTime ftime = Convert.ToDateTime(dt_finishTime.Rows[0]["classEndtime"].ToString()).AddSeconds(Form1.sdsModel.TimeSetE).AddSeconds(10);
                try
                {
                    int timeNum = Convert.ToInt32(dt_finishTime.Rows[0]["time_num"].ToString());
                    string endtime = Convert.ToDateTime(dt_finishTime.Rows[0]["classEndtime"]).ToString("yyyy-MM-dd");
                    string groupId = dt_finishTime.Rows[0]["class_sgroupid"].ToString();
                    tnModel = new TaskNumModel(taskClassId, taskNumId, timeNum, 0, endtime, groupId);
                }
                catch (Exception ex)
                {
                    LogUtil.LogToFile(ex, this.GetType().Name);
                }

                return tnModel;
            }
            else
            {
                ////    调课
                //finishtimesql = @"SELECT [time_Etime],[time_Stime] FROM[AccessDB].[dbo].[R_Times]  
                //                 where time_num = (SELECT[scs_time] FROM[PhoneSign].[dbo].[P_SwitchCourseSign]  where scs_TaskNum = '" + taskNumId + "') ";

                finishtimesql = @"SELECT [scs_time],scs_Data,scs_GroupId FROM[PhoneSign].[dbo].[P_SwitchCourseSign]  where scs_TaskNum = '" + taskNumId + "' ";

                dt_finishTime = DBdao.Query_dt(finishtimesql);
                if (dt_finishTime.Rows.Count > 0)
                {
                    try
                    {
                        int timeNum = Convert.ToInt32(dt_finishTime.Rows[0]["scs_time"].ToString());
                        string endtime = Convert.ToDateTime(dt_finishTime.Rows[0]["scs_Data"]).ToString("yyyy-MM-dd"); //2018-04-28 08:25:47.493
                        string groupId = dt_finishTime.Rows[0]["scs_GroupId"].ToString();
                        tnModel = new TaskNumModel(taskClassId, taskNumId, timeNum, 1, endtime, groupId);
                    }
                    catch (Exception ex)
                    {
                        LogUtil.LogToFile(ex, this.GetType().Name);
                    }
                }
                return tnModel;
                //DateTime ftime = dt_finishTime.Rows.Count > 0 ? Convert.ToDateTime(dt_finishTime.Rows[0]["time_Etime"].ToString()).AddSeconds(Form1.sdsModel.TimeSetE).AddSeconds(10) : DateTime.Now.AddSeconds(100);
                // DateTime.Parse(ftime).AddHours(+2d);
                // string  stime = dt_finishTime.Rows.Count > 0 ? dt_finishTime.Rows[0]["time_Stime"].ToString() : startTime;     ////// 日期格式不正确
                // TaskNumModel tnModel = new TaskNumModel(classList, taskNumId, ftime.ToString(),1);
                //TaskNumModel tnModel = new TaskNumModel(classId, taskNumId, DateTime.Now.AddMinutes(2).ToString(), startTime);
                // tnModel.taskNumType = 1;   // 调课
            }
            // }
        }

        /// <summary>
        /// 获取该学生所在的所有课程（包括必修，选修）
        /// </summary>
        /// <param name="phone"></param>
        /// <returns></returns>
        public string getClassIdList(int userId)
        {

            string taskClassId = string.Empty;
            string sqlstr = @"SELECT  [Pf_CValue]  FROM [AccessDB].[dbo].[R_ProfileN]   
                    where (Pf_CName = 'EClassId'  or  Pf_CName = 'Class_id' ) and Pf_UserId = " + userId + "";

            DataTable dt_TaskClassId = DBdao.Query_dt(sqlstr);

            if (dt_TaskClassId.Rows.Count > 0)
            {
                for (int i = 0; i < dt_TaskClassId.Rows.Count; i++)
                {
                    if (i == 0)
                    {
                        taskClassId += dt_TaskClassId.Rows[i]["Pf_CValue"].ToString();
                    }
                    else
                    {
                        taskClassId += "," + dt_TaskClassId.Rows[i]["Pf_CValue"].ToString();
                    }
                }
            }
            return taskClassId;
        }


        /// <summary>
        /// 获取课程对应的班级
        /// 课程的状态:掉课，正常
        /// 课程的类型：选修，必修
        /// </summary>
        /// <param name="taskNumId"></param>
        /// <returns></returns>
        public String getTaskClassId(string taskNumId)
        {
            string taskClassId = "SELECT  [task_classid]  FROM [AccessDB].[dbo].[TaskBindNum]  where  Stb_ScanTaskNum =  '" + taskNumId + "' ";
            DataTable dt_taskClassId = DBdao.Query_dt(taskClassId);

            if (dt_taskClassId.Rows.Count > 0)
            {
                String classId = dt_taskClassId.Rows[0]["task_classid"].ToString();
                return classId;
            }
            //调课
            else
            {
                string SwitchClassId = "SELECT [scs_ClassId]  FROM [PhoneSign].[dbo].[P_SwitchCourseSign]  where [scs_TaskNum] = '" + taskNumId + "' ";
                DataTable dt_switchClassId = DBdao.Query_dt(SwitchClassId);
                try
                {
                    String classId = dt_switchClassId.Rows[0]["scs_ClassId"].ToString();
                    return classId;
                }
                catch (Exception ex)
                {
                    LogUtil.LogToFile(ex, this.GetType().Name);
                    return null;
                }
            }
        }

        /// <summary>
        /// 更新signtime的状态  
        /// </summary>
        /// <param name="setData"></param>    修改后的状态
        /// <param name="whereData"></param>   需要修改的状态
        public void updateIsHandle(int setData, int whereData, string taskNum, string signTime)
        {
            string where = taskNum.Equals("NULL") ? " and  sthd_ScanTaskNum is null" : " and sthd_ScanTaskNum = '" + taskNum + "'";
            string updatesql = "update [PhoneSign].[dbo].[P_SignTimeHandle]  set sthd_IsHandle=" + setData + "  where  [sthd_IsHandle]= " + whereData + " " + where + "  and sthd_Scantime < '" + signTime + "' ";
            // string updatesql = "update [PhoneSign].[dbo].[P_SignTime]  set ishandle=" + setData + "  where  [IsHandle]= " + whereData + " " + where + "";
            DBdao.Execute(updatesql, Form1.connPhoneSign);
        }

        public void updateIsHandle(int setData, int whereData)
        {
            string updatesql = "update [PhoneSign].[dbo].[P_SignTimeHandle]  set sthd_IsHandle=" + setData + "  where  [sthd_IsHandle]= " + whereData + "";
            DBdao.Execute(updatesql, Form1.connPhoneSign);
        }


        public void removeSignTime()
        {
            DataBaseDao PhoneSignDBdao = new DataBaseDao(Form1.connPhoneSign);
            string sqlstr = @"  Select  [st_sgroupId],[st_id],[st_userid],[IsHandle] ,[st_ScanTime]  from [PhoneSign].[dbo].[P_SignTime] where [st_id] in (
            SELECT [st_id]  FROM [PhoneSign].[dbo].[P_SignTime] except (select  [st_id]   from[PhoneSign].[dbo].[P_SignTime], p_signtimehandle where p_signtime.st_id = p_signtimehandle.sthd_stid ))  order by st_id";

            DataTable dt_SignTime = PhoneSignDBdao.Query_dt(sqlstr);
            foreach (DataRow dr in dt_SignTime.Rows)
            {
                try
                {

                    int st_id = Convert.ToInt32(dr["st_id"] ?? 0);
                    int IsHandle = Convert.ToInt32(dr["IsHandle"]);
                    int st_userid = 0;
                    if (!string.IsNullOrEmpty(dr["st_userid"].ToString()))
                    {
                        st_userid = Convert.ToInt32(dr["st_userid"]);
                    }

                    string groupId = dr["st_sgroupId"].ToString() ?? "";

                    string scanTime = dr["st_ScanTime"].ToString();

                    List<string> TaskIdList = getTaskId(st_userid, scanTime, groupId);

                    string insertIsHandle = string.Empty;
                    if (TaskIdList.Count > 0)
                    {
                        //遍历
                        //foreach (String taskId in TaskIdList)
                        // {
                        insertIsHandle = @" INSERT INTO  [PhoneSign].[dbo].[P_SignTimeHandle] ([sthd_stid],[sthd_IsHandle],[sthd_ScanTaskNum],[sthd_Scantime]) VALUES (" + st_id + "," + IsHandle + ",'" + TaskIdList[0] + "','" + scanTime + "') ";
                        LogUtil.SaveLog("写入SignTImeHandle Sql ：" + insertIsHandle + "人", "Main");
                        PhoneSignDBdao.Execute(insertIsHandle, Form1.connPhoneSign);
                        //}
                    }

                    else
                    {
                        insertIsHandle = @" INSERT INTO  [PhoneSign].[dbo].[P_SignTimeHandle] ([sthd_stid],[sthd_IsHandle],[sthd_ScanTaskNum],[sthd_Scantime]) VALUES (" + st_id + "," + IsHandle + ",'NULL','" + scanTime + "') ";
                        PhoneSignDBdao.Execute(insertIsHandle, Form1.connPhoneSign);
                    }
                    
                }
                catch (Exception ex)
                {
                    LogUtil.LogToFile(ex, this.GetType().Name);
                }
            }
        }


        public List<string> getTaskId(int userId, string scanTime, string groupId)
        {

            List<string> taskIdNumList = new List<string>();

            string TaskClassId = getClassIdList(userId);
            if (TaskClassId == null || TaskClassId.Equals(""))
            {
                return taskIdNumList;
            }

            DateTime time = Convert.ToDateTime(scanTime);
            String dt = time.ToString("HH:mm:ss");


            //string sqlstr = @"SELECT  [Pf_CValue]  FROM [AccessDB].[dbo].[R_ProfileN]   
            //        where (Pf_CName = 'EClassId') and Pf_UserId = " + userId + "";

            //DataTable dt_TaskClassId = DBdao.Query_dt(sqlstr);


          
            //string eclassId = "''";
            //if (dt_TaskClassId.Rows.Count > 0)
            //{
            //    eclassId = dt_TaskClassId.Rows[0]["Pf_CValue"].ToString();
            //}


            string sGetMac = @" SELECT [Stb_ScanTaskNum] FROM [AccessDB].[dbo].[TaskBindNum]
                where exists   (SELECT  time_num FROM[AccessDB].[dbo].[R_Times] where DATEDIFF([minute], (SELECT DATEADD(SECOND, -(SELECT[set_TimeSetS] FROM [PhoneSign].[dbo].[P_Set]) ,time_Stime)) , (Select CONVERT(varchar(100),'" + dt + "', 8))) >=0 " +
              " AND (task_classid in (" + TaskClassId + ")) AND DATEDIFF([minute], (SELECT DATEADD(SECOND, (SELECT[set_TimeSetE] FROM [PhoneSign].[dbo].[P_Set]) ,time_Etime)), (Select CONVERT(varchar(100),'" + dt + "', 8))) <=0 and time_view = 1  and time_SgroupId = '" + groupId + "')  and" +
              "[Stb_TaskWeek] = (select AccessDB.dbo.getCurrentWeek('" + scanTime + "','" + groupId + "'))  and task_weekId = (SELECT  datepart(weekday, '" + scanTime + "') - 1)  and sd_termId = (SELECT[t_Num]" +
              "FROM[AccessDB].[dbo].[Term] where t_BeginTime<='" + scanTime + "' and t_EndTime >= '" + scanTime + "'  and  sy_SgroupId = '" + groupId + "') " +
              "and sd_syId = (SELECT sy_id FROM [AccessDB].[dbo].[Term]  where t_BeginTime <= '" + scanTime + "' and t_EndTime >= '" + scanTime + "')"
              ;


            //          string sGetMac = @"   select  [Stb_ScanTaskNum] from [AccessDB].[dbo].[TaskBindNum] where [class_SgroupId]=" + groupId + @" and  '" + scanTime + @"' between DATEADD( SECOND,-(SELECT[set_TimeSetS]
            //FROM [PhoneSign].[dbo].[P_Set]),cast(substring([classdate],1,19) as datetime)) and DATEADD( SECOND,+(SELECT[set_TimeSetS]
            //FROM [PhoneSign].[dbo].[P_Set]),cast(substring([classEndtime],1,19) as datetime)) and 
            //sd_syId= (SELECT sy_id FROM [AccessDB].[dbo].[Term]  where '" + scanTime + @"' between t_BeginTime and t_EndTime  and sy_SgroupId = " + groupId + @") and
            //[sd_termId]=(SELECT[t_Num]FROM[AccessDB].[dbo].[Term] where '" + scanTime + @"' between t_BeginTime and t_EndTime  and  sy_SgroupId = " + groupId + @") and
            //(task_classid in ("+ eclassId + ")  or  task_classid = (select class_id from [AccessDB].[dbo].Student where u_id='" + userId + "'))";


            DataTable dt_Mac = DBdao.Query_dt(sGetMac); //获取当前手机该时段的课程终端ID及TaskID

            if (dt_Mac.Rows.Count > 0)    //有课程任务
            {
                taskIdNumList.Add(dt_Mac.Rows[0]["Stb_ScanTaskNum"].ToString()); //有且仅有一条正在上课的数据
                //foreach (DataRow dr in dt_Mac.Rows)
                //{
                //    taskIdNumList.Add(dr["Stb_ScanTaskNum"].ToString()); //有且仅有一条正在上课的数据
                //}
            }

            //调课
            string sClassSwitch = @"SELECT scs_TaskNum FROM[PhoneSign].[dbo].[P_SwitchCourseSign] where DateDiff(dd, scs_Data,'" + scanTime + "')=0 and (scs_ClassId in ('" + TaskClassId + "'))" +
               "and scs_Bind = 0 and scs_time in (SELECT time_num FROM[AccessDB].[dbo].[R_Times] where DATEDIFF([minute], (SELECT DATEADD(SECOND, -(SELECT[set_TimeSetS] FROM [PhoneSign].[dbo].[P_Set]) ,time_Stime)) , (Select CONVERT(varchar(100),'" + dt + "', 8))) >=0 AND DATEDIFF([minute], (SELECT DATEADD(SECOND, (SELECT[set_TimeSetE] FROM [PhoneSign].[dbo].[P_Set]) ,time_Etime)), (Select CONVERT(varchar(100), '" + dt + "', 8))) <= 0 and time_view = 1)";
            DataTable dt_ClassSwitch = DBdao.Query_dt(sClassSwitch);
            if (dt_ClassSwitch.Rows.Count > 0)
            {
                taskIdNumList.Add(dt_ClassSwitch.Rows[0]["scs_TaskNum"].ToString()); //有且仅有一条正在上课的数据
            }
            return taskIdNumList;
        }

        public void removeManualSign(string TaskNumId)
        {

            DataBaseDao PhoneSignDBdao = new DataBaseDao(Form1.connPhoneSign);
            try
            {
                string sql = @"  INSERT INTO [PhoneSign].[dbo].[P_SignReSolved] ( [srs_TaskNum],[srs_StuId],[srs_SignResult],[srs_ClassId] ,[srs_ClassEndTime]) 
                             SELECT [ms_TaskNum]   ,[ms_StuId],[ms_Status],[ms_ClassId],[ms_ClassEndtime]  FROM [PhoneSign].[dbo].[P_ManualSign]  where  [ms_TaskNum] = '" + TaskNumId + "' ";
                PhoneSignDBdao.Execute(sql, Form1.connPhoneSign);

                string updateStr = @" update [PhoneSign].[dbo].[P_ManualSign] set  [ms_Solved] = 1  where   [ms_TaskNum] = '" + TaskNumId + "'";
                DBdao.Execute(updateStr, Form1.connPhoneSign);

            }
            catch (Exception ex)
            {
                LogUtil.LogToFile(ex, this.GetType().Name);
            }
        }




        /// <summary>
        /// 获取 signtime 数据
        /// 
        /// </summary>
        public List<SignTimeList> getUserSignTime(string time)
        {
            string where = time == null ? "" : "and  st_ScanTime > '" + time + "' ";
            //string stusql = @"select  class_id,st_userid,  st_scanStatus,  st_ScanTime, St_ScanTaskNum,  St_SsidScan from [PhoneSign].[dbo].[StudentSignTime] 
            //                        where  st_scanStatus = 1  and St_ScanTaskNum != 'NULL'  and St_SsidScan<>'0' and IsHandle=2  " + where + " order by  st_ScanTime";
            string stusql = @"SELECT [st_userid],[sthd_ScanTaskNum],[st_ScanTime],[st_scanStatus],[St_SsidScan],[st_sgroupId] FROM [PhoneSign].[dbo].[SignTimeBindHandle] 
                            where  st_scanStatus = 1  and [sthd_ScanTaskNum] != 'NULL'  and St_SsidScan<>'0' and [sthd_IsHandle]=2  " + where + "  order by st_sgroupId,st_ScanTime";


            string groupIdsql = @"SELECT [st_sgroupId] FROM [PhoneSign].[dbo].[SignTimeBindHandle] 
                            where  st_scanStatus = 1  and [sthd_ScanTaskNum] != 'NULL'  and St_SsidScan<>'0' and [sthd_IsHandle]=2  " + where + "  group by st_sgroupId";


            DataTable dt_SignTime = DBdao.Query_dt(stusql);
            DataTable dt_groupId = DBdao.Query_dt(groupIdsql);
            List<SignTimeList> signTimeLists = new List<SignTimeList>();

            for (int i = 0; i < dt_groupId.Rows.Count; i++)
            {

                String groupId = dt_groupId.Rows[i]["st_sgroupId"].ToString();
                SignTimeList signTimes = new SignTimeList();
                signTimes.groupId = groupId;

                List<DataRow> dr_SignTime = dt_SignTime.Select("st_sgroupId = '" + groupId + "'").ToList();

                for (int j = 0; j < dr_SignTime.Count; j++)
                {
                    SignTimeModel signTime = new SignTimeModel();

                    signTime.userId = dr_SignTime[j]["st_userid"].ToString();
                    signTime.taskNum = dr_SignTime[j]["sthd_ScanTaskNum"].ToString();
                    signTime.scanTime = Convert.ToDateTime(dr_SignTime[j]["st_ScanTime"]).ToString("yyyy-MM-dd HH:mm:ss.fff");
                    signTime.scanStatus = dr_SignTime[j]["st_scanStatus"].ToString();
                    signTime.ssidScan = dr_SignTime[j]["St_SsidScan"].ToString();

                    signTimes.signtimes.Add(signTime);

                    dr_SignTime.ToString();
                }
                signTimeLists.Add(signTimes);
            }
            return signTimeLists;
        }

        /// <summary>
        /// 插入事件
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="EventNum"></param>
        /// <param name="StuList"></param>
        public void insertEven(TaskNumModel taskNumModel, SignEven signEven, List<SignTimeModels> StuList, List<Termenal> terlist, SystemDataSetModel sdsModel, string timehold)
        {
            try
            {
                string sEvenNum = @"SELECT [e_EventNum] FROM [PhoneSign].[dbo].[P_Events] where [e_EventNum] = '" + signEven.EventNum + "' ";
                DataTable dt_EvenNum = DBdao.Query_dt(sEvenNum);

                //是否已存在
                if (dt_EvenNum.Rows.Count == 0)
                {
                    string insertEvent = @"insert into [PhoneSign].[dbo].[P_Events] (e_EventNum, e_RoomId, e_SSID, e_RcTime, e_TimeThresHold, e_TimeRange, e_TaskNum,e_ClassId,E_Status)values('" + signEven.EventNum + "',(select At_RoomId from [PhoneSign].[dbo].[P_AgentTerminal] where  At_TerminalId like '%" + terlist[0].termenal + "%'),'" + string.Join(",", terlist.Select(u => u.termenal).ToArray()) + "','" + signEven.Rctime + "'," + sdsModel.holdtime + ",'" + timehold + "','" + taskNumModel.taskNumId + "'," + taskNumModel.classId + "," + signEven.EvenStatus + ")";
                    LogUtil.SaveLog("\r\n写入时间：" + DateTime.Now.ToString() + "insertEvent: " + insertEvent + "\r\n sEvenNum: " + sEvenNum + "\r\n dt_EvenNum.Rows.Count :" + dt_EvenNum.Rows.Count, "Main");
                    DBdao.Execute(insertEvent, Form1.connPhoneSign);
                    foreach (SignTimeModels st in StuList)
                    {
                        string insertEvenuser = "insert into [PhoneSign].[dbo].[P_EventsUser] (eu_EventsId, eu_UserId, eu_Status) values('" + signEven.EventNum + "'," + st.userid + "," + st.scanStatus + ")";
                        DBdao.Execute(insertEvenuser, Form1.connPhoneSign);
                    }


                    string sStuId = "select [srs_StuId] FROM [PhoneSign].[dbo].[P_SignReSolved]  where [srs_TaskNum] = '" + taskNumModel.taskNumId + "'";

                    DataTable dt_StuId = DBdao.Query_dt(sStuId);

                    foreach (DataRow dr in dt_StuId.Rows)
                    {
                        try
                        {

                            string sqlstr = "select  count(*)count FROM [PhoneSign].[dbo].[P_EventsUser] where eu_UserId = '" + dr["srs_StuId"] + "'";
                            DataTable dt_sqlstr = DBdao.Query_dt(sqlstr);
                            if (Convert.ToInt32(dt_sqlstr.Rows[0]["count"]) == 0)
                            {
                                string iEvenuser = "insert into [PhoneSign].[dbo].[P_EventsUser] (eu_EventsId, eu_UserId, eu_Status) values('" + signEven.EventNum + "'," + dr["srs_StuId"] + ",0)";
                                DBdao.Execute(iEvenuser, Form1.connPhoneSign);
                            }
                        }
                        catch (Exception ex)
                        {
                            LogUtil.LogToFile(ex, this.GetType().Name);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogUtil.LogToFile(ex, this.GetType().Name);
            }
            //if (bol)
            //{
            //    string updateSwitch = " update[PhoneSign].[dbo].[P_SwitchCourseSign] set scs_Bind = 2 where scs_Bind = 0 and scs_TaskNum = '" + taskNum + "' ";
            //    DBdao.Execute(updateSwitch);
            //}

        }


        ///查询上课班级学生 
        public List<int> getUserId(string classId)
        {
            try
            {
                List<int> uidList = new List<int>();
                string sStudent = "select [u_id] from [AccessDB].[dbo].[Student] where [class_id]=" + classId;
                DataTable dt = DBdao.Query_dt(sStudent);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        uidList.Add(Convert.ToInt32(dr["u_id"]));
                    }

                }
                else
                {
                    // 走班制
                    string sSwitchStudent = "select u_id from[AccessDB].[dbo].[ElectiveStu] ('" + classId + "') ";
                    DataTable dt_Switch = DBdao.Query_dt(sSwitchStudent);

                    foreach (DataRow dr in dt_Switch.Rows)
                    {
                        uidList.Add(Convert.ToInt32(dr["u_id"]));
                    }
                }



                return uidList;

            }
            catch (Exception ex)
            {
                LogUtil.LogToFile(ex, this.GetType().Name);
                return null;
            }

        }


        ///查询学生所在班级 
        public string getClassId(string userID)
        {
            string ClassId = string.Empty;
            string sStudent = @"SELECT [class_id] FROM[AccessDB].[dbo].[Student]  where u_Phone = '" + userID + "'";
            DataTable dt = DBdao.Query_dt(sStudent);
            try
            {

                foreach (DataRow dr in dt.Rows)
                {
                    ClassId = dr["class_id"].ToString();
                }
            }
            catch (Exception ex)
            {
                LogUtil.LogToFile(ex, this.GetType().Name);
            }
            return ClassId;
        }


        //528
        public void insert(string sSql, string iSql, string taskNumId, int taskNumType)
        {

            if (DBdao.Query_dt(sSql).Rows.Count == 0)
            {
                DBdao.Execute(iSql, Form1.connPhoneSign);

                if (taskNumType == 1)
                {
                    string updateSwitch = " update[PhoneSign].[dbo].[P_SwitchCourseSign] set scs_Bind = 1 where scs_Bind = 0 and scs_TaskNum = '" + taskNumId + "' ";
                    DBdao.Execute(updateSwitch, Form1.connPhoneSign);
                }
            }
        }


        /// <summary>
        /// 返回 该签到的人数  （除去手动签到的人）
        /// </summary>
        /// <param name="taskNumId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>

        public bool getManualSignCount(string taskNumId, string classId, int signCount, int usecoutn)
        {

            string sql_studentCount = "SELECT count(*)count FROM[AccessDB].[dbo].[Student]  where class_id = '" + classId + "'";

            string sql_ManualCount = @"  select  count(*)count FROM   [PhoneSign].[dbo].[P_ManualSign]  where [ms_TaskNum] = '" + taskNumId + "'";


            DataTable dt_ManualCount = DBdao.Query_dt(sql_ManualCount);
            DataTable dt_studentCount = DBdao.Query_dt(sql_studentCount);


            try
            {
                int manualCount = Convert.ToInt32(dt_ManualCount.Rows[0]["count"]);

                int StudentNumber = Convert.ToInt32(Convert.ToInt32(dt_studentCount.Rows[0]["count"]));
                if (StudentNumber == 0)
                {
                    string sql = "select count(*)count from [AccessDB].[dbo].[ElectiveStu] ('" + classId + "')";  /// 走班制
                    DataTable dt = DBdao.Query_dt(sql);
                    StudentNumber = Convert.ToInt32(Convert.ToInt32(dt.Rows[0]["count"]));
                }


                int i = (manualCount + signCount);
                LogUtil.SaveLog("手动签到人数：" + manualCount + "人", "Main");
                double h = StudentNumber * (usecoutn * 0.01);

                bool bol = i >= h;

                return bol;


            }
            catch (Exception ex)
            {
                LogUtil.LogToFile(ex, this.GetType().Name);
                return false;
            }

        }


        public void HandleManualSign(string time)
        {

            string sql = @"select  [ms_GroupId],[ms_TaskNum],TaskNumCount ,[scs_ClassId],[task_classid] from (
                           select  [ms_GroupId],[ms_TaskNum] , count([ms_TaskNum])TaskNumCount  from  [PhoneSign].[dbo].[P_ManualSign]  where  [ms_Solved] = 0  and 
                           DATEADD ( ss ,200 ,[ms_ClassEndtime])  <    '" + time + "'    group by  [ms_TaskNum], [ms_GroupId])s " +
                            "left join   [AccessDB].[dbo].[TaskBindNum]t  on  t.Stb_ScanTaskNum = s.[ms_TaskNum]  	" +
                            "left join   [PhoneSign].[dbo].[P_SwitchCourseSign]p on p.scs_TaskNum = s.[ms_TaskNum]";

            string setsql = @"SELECT [set_StartEvenNum],[set_TimeThresHold],[set_TerminalCheNum],[set_TerminalStuChe],[set_TimeSetS],[set_TimeSetE],[set_SgroupId] FROM [PhoneSign].[dbo].[P_Set]";

            DataTable dt = DBdao.Query_dt(sql);

            try
            {
                foreach (DataRow dr in dt.Rows)
                {
                    string ClassId = string.Empty;
                    ClassId = dr["task_classid"].ToString();
                    if (ClassId == null || ClassId == "" || ClassId.Equals("NULL"))
                    {
                        ClassId = dr["scs_ClassId"].ToString();
                    }
                    string taskNumId = dr["ms_TaskNum"].ToString();
                    int TaskNumCount = Convert.ToInt32(dr["TaskNumCount"]);

                    List<int> uidList = getUserId(ClassId);

                    // 是否超过阀值
                    int usecoutn = Form1.sdsModel.Where(s => s.GroupId.Equals(dr["ms_GroupId"].ToString())).ToList()[0].usecoutn;
                    double h = uidList.Count * (usecoutn * 0.01);
                    bool bol = TaskNumCount >= h;
                    if (bol)
                    {
                        string removeManualSign = @"insert into[PhoneSign].[dbo].[P_SignReSolved] (srs_TaskNum, srs_StuId, srs_SignResult, srs_ClassId,[srs_ClassEndTime]) SELECT[ms_TaskNum]
                                               ,[ms_StuId]
                                               ,[ms_Status]
                                               ,[ms_ClassId]
                                               ,[ms_ClassEndtime]
                                               FROM[PhoneSign].[dbo].[P_ManualSign] where[ms_TaskNum] = '" + taskNumId + "'";

                        DBdao.Execute(removeManualSign, Form1.connPhoneSign);

                        foreach (int id in uidList)
                        {
                            string insertResult = "insert into [PhoneSign].[dbo].[P_SignReSolved] (srs_TaskNum, srs_StuId, srs_SignResult, srs_RoomId, srs_ClassId,srs_Uptime,[srs_ClassEndTime])values('" + taskNumId + "'," + id + "," + 4 + ",'" + 0 + "'," + ClassId + ",GETDATE(), (SELECT  distinct [ms_ClassEndtime]  FROM [PhoneSign].[dbo].[P_ManualSign]  where   [ms_TaskNum] = '" + taskNumId + "'))";
                            DBdao.Execute(insertResult, Form1.connPhoneSign);
                        }
                    }

                    string updateStr = @" update [PhoneSign].[dbo].[P_ManualSign] set  [ms_Solved] = 1  where   [ms_TaskNum] = '" + taskNumId + "'";
                    DBdao.Execute(updateStr, Form1.connPhoneSign);
                }
            }
            catch (Exception ex)
            {
                LogUtil.LogToFile(ex, this.GetType().Name);
            }
        }





    }
}